USE QuanLySieuThi
GO
CREATE PROC sp_TruKho
	@MaKho varchar(50),
	@MaHangHoa varchar(50),
	@SoLuong float
AS
BEGIN
	Declare @sl int
	Select @sl=SoLuong from ChiTietKhoHang Where MaKho=@MaKho and MaHangHoa=@MaHangHoa and Deleted=N'False'
	Update ChiTietKhoHang
	Set SoLuong=(@sl-@SoLuong)
	Where MaKho=@MaKho and MaHangHoa=@MaHangHoa and Deleted=N'False'
END
GO
CREATE PROC sp_CongKho
	@MaKho varchar(50),
	@MaHangHoa varchar(50),
	@SoLuong float
AS
BEGIN
	Declare @sl int
	Select @sl=SoLuong from ChiTietKhoHang Where MaKho=@MaKho and MaHangHoa=@MaHangHoa and Deleted=N'False'
	Update ChiTietKhoHang
	Set SoLuong=(@sl+@SoLuong)
	Where MaKho=@MaKho and MaHangHoa=@MaHangHoa and Deleted=N'False'
END
GO
CREATE PROC sp_Select_NhanVien
	@HanhDong nvarchar(50),
	@MaNhanVien	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From NhanVien
	END
	IF @HanhDong='1'
	BEGIN
		Select * From NhanVien Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From NhanVien Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From NhanVien Where Deleted='False' AND MaNhanVien=@MaNhanVien
	END
END
GO
CREATE PROC sp_InsertUpdate_NhanVien
	@HanhDong nvarchar(50),
	@MaNhanVien	varchar(50),
	@TenNhanVien	nvarchar(200),
	@NgaySinh datetime,	
	@GioiTinh bit,
	@MaPhongBan	varchar(50),
	@TenPhongBan	nvarchar(200),
	@DCThuongTru	nvarchar(200),
	@DCTamTru	nvarchar(200),
	@DienThoaiCD	varchar(20),
	@DienThoaiDD	varchar(20),
	@Email	nvarchar(50),
	@CMND	nvarchar(20),
	@NgayCap	datetime,
	@NoiCap	nvarchar(200),	
	@LinkImage nvarchar(200),
	@GhiChu	nvarchar(200),
	@Deleted	bit
AS
BEGIN
	IF @HanhDong='Insert'
	BEGIN
		Insert Into NhanVien
		Values(@MaNhanVien,
				@TenNhanVien,
				@NgaySinh,	
				@GioiTinh,
				@MaPhongBan,
				@TenPhongBan,
				@DCThuongTru,
				@DCTamTru,
				@DienThoaiCD,
				@DienThoaiDD,
				@Email,
				@CMND,
				@NgayCap,
				@NoiCap,	
				@LinkImage,
				@GhiChu,
				@Deleted)
	END
	IF @HanhDong='Update'
	BEGIN
		Update NhanVien
		Set TenNhanVien=@TenNhanVien,
			NgaySinh=@NgaySinh,	
			GioiTinh=@GioiTinh,
			MaPhongBan=@MaPhongBan,
			TenPhongBan=@TenPhongBan,
			DCThuongTru=@DCThuongTru,
			DCTamTru=@DCTamTru,
			DienThoaiCD=@DienThoaiCD,
			DienThoaiDD=@DienThoaiDD,
			Email=@Email,
			CMND=@CMND,
			NgayCap=@NgayCap,
			NoiCap=@NoiCap,	
			LinkImage=@LinkImage,
			GhiChu=@GhiChu,
			Deleted=@Deleted
		Where MaNhanVien=@MaNhanVien AND Deleted='False'
	END
END
GO
CREATE PROC sp_Deleted_NhanVien
	@HanhDong nvarchar(50),
	@MaNhanVien	varchar(50)
AS
BEGIN
	IF @HanhDong='Update'
	BEGIN
		Update NhanVien
		Set Deleted='True'
		Where MaNhanVien=@MaNhanVien AND Deleted='False'
	END
	IF @HanhDong='Restart'
	BEGIN
		Update NhanVien
		Set Deleted='False'
		Where MaNhanVien=@MaNhanVien AND Deleted='True'
	END
	IF @HanhDong='Deleted'
	BEGIN
		Delete From NhanVien Where MaNhanVien=@MaNhanVien
	END
END
GO
CREATE PROC sp_Select_HoaDonNhap
	@HanhDong nvarchar(50),
	@MaHoaDonNhap	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From HoaDonNhap
	END
	IF @HanhDong='1'
	BEGIN
		Select * From HoaDonNhap Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From HoaDonNhap Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From HoaDonNhap Where Deleted='False' AND MaHoaDonNhap=@MaHoaDonNhap
	END
END
GO
CREATE PROC sp_InsertUpdate_HoaDonNhap
	@HanhDong nvarchar(50),
	@MaHoaDonNhap	varchar(50),
	@NgayNhap	Datetime,
	@MaNhaCungCap	varchar(50),
	@TenNhaCungCap nvarchar(200),
	@NoHienThoi	float,
	@NguoiGiaoHang	nvarchar(200)	,
	@HinhThucThanhToan	nvarchar(20),
	@MaKho	varchar(50),
	@TenKho nvarchar(200),
	@HanThanhToan	Datetime,
	@MaDonDatHang	varchar(50),
	@NgayDat datetime,
	@MaTienTe	varchar(50),
	@TyGiaTienTe		float,
	@ChietKhau	float,
	@ThanhToanNgay	float	,
	@ThueGTGT	varchar(50),
	@TongTien	float,
	@ThanhToanSauKhiLapPhieu float,
	@MaNhanVien	varchar(50),
	@TenNhanVien	nvarchar(200),
	@GhiChu	nvarchar(200),
	@Deleted	bit	
AS
BEGIN
	IF @HanhDong='Insert'
	BEGIN
		Insert Into HoaDonNhap
		Values(@MaHoaDonNhap,
				@NgayNhap,
				@MaNhaCungCap,
				@TenNhaCungCap,
				@NoHienThoi,
				@NguoiGiaoHang,
				@HinhThucThanhToan,
				@MaKho,
				@TenKho,
				@HanThanhToan,
				@MaDonDatHang,
				@NgayDat,
				@MaTienTe,
				@TyGiaTienTe,
				@ChietKhau,
				@ThanhToanNgay,
				@ThueGTGT,
				@TongTien,
				@ThanhToanSauKhiLapPhieu,
				@MaNhanVien,
				@TenNhanVien,
				@GhiChu,
				@Deleted)
	END
END
GO
CREATE PROC sp_Select_NhaCungCap
	@HanhDong nvarchar(50),
	@MaNhaCungCap	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From NhaCungCap
	END
	IF @HanhDong='1'
	BEGIN
		Select * From NhaCungCap Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From NhaCungCap Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From NhaCungCap Where Deleted='False' AND MaNhaCungCap=@MaNhaCungCap
	END
END
GO
CREATE PROC sp_InsertUpdate_NhaCungCap
	@HanhDong nvarchar(50),
	@MaNhaCungCap nvarchar(50) ,
	@TenNhaCungCap	nvarchar(200),
	@NoHienThoi float,
	@DiaChi	nvarchar(200),
	@DienThoai	nvarchar(20),
	@Email	nvarchar(50),
	@Website	varchar(200),
	@Fax	nvarchar(20),
	@NguoiLienHe	nvarchar(200),
	@MaSoThue	nvarchar(20),
	@LinkImage nvarchar(200),
	@GhiChu	nvarchar(200),
	@Deleted	bit	
AS
BEGIN
	IF @HanhDong='Insert'
	BEGIN
		Insert Into NhaCungCap
		Values(@MaNhaCungCap,@TenNhaCungCap,@NoHienThoi,@DiaChi,@DienThoai,@Email,@Website,@Fax,@NguoiLienHe,@MaSoThue,@LinkImage,@GhiChu,@Deleted)
	END
	IF @HanhDong='Update'
	BEGIN
		Update NhaCungCap
		Set TenNhaCungCap=@TenNhaCungCap,
			NoHienThoi=@NoHienThoi,
			DiaChi=@DiaChi,
			DienThoai=@DienThoai,
			Email=@Email,
			Website=@Website,
			Fax=@Fax,
			NguoiLienHe=@NguoiLienHe,
			MaSoThue=@MaSoThue,
			LinkImage=@LinkImage,
			GhiChu=@GhiChu,
			Deleted=@Deleted
		Where MaNhaCungCap=@MaNhaCungCap AND Deleted='False'
	END
END
GO
CREATE PROC sp_Deleted_NhaCungCap
	@HanhDong nvarchar(50),
	@MaNhaCungCap	varchar(50)
AS
BEGIN
	IF @HanhDong='Update'
	BEGIN
		Update NhaCungCap
		Set Deleted='True'
		Where MaNhaCungCap=@MaNhaCungCap AND Deleted='False'
	END
	IF @HanhDong='Restart'
	BEGIN
		Update NhaCungCap
		Set Deleted='False'
		Where MaNhaCungCap=@MaNhaCungCap AND Deleted='True'
	END
	IF @HanhDong='Deleted'
	BEGIN
		Delete From NhaCungCap Where MaNhaCungCap=@MaNhaCungCap
	END
END
GO
CREATE PROC sp_Select_KhachHangTraLaiBanLe
	@HanhDong nvarchar(50),
	@MaKhachHangTraLaiBanLe	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From KhachHangTraLaiBanLe
	END
	IF @HanhDong='1'
	BEGIN
		Select * From KhachHangTraLaiBanLe Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From KhachHangTraLaiBanLe Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From KhachHangTraLaiBanLe Where Deleted='False' AND MaKhachHangTraLaiBanLe=@MaKhachHangTraLaiBanLe
	END
END
GO
CREATE PROC sp_InsertUpdate_KhachHangTraLaiBanLe
	@HanhDong nvarchar(50),
	@MaKhachHangTraLaiBanLe	varchar(50),
	@MaKhachHang	varchar(50),
	@TenKhachHang nvarchar(200),
	@MaKho	varchar(50),
	@TenKho  nvarchar(200),
	@MaNhanVien	varchar(50),
	@TenNhanVien	nvarchar(200),
	@NgayNhap	Datetime,
	@NguoiTra	nvarchar(200),
	@MaTienTe	varchar(50),
	@TyGiaTienTe	float,
	@TienBoiThuong float,
	@GhiChu	nvarchar(200),
	@Deleted	bit
AS
BEGIN
	IF @HanhDong='Insert'
	BEGIN
		Insert Into KhachHangTraLaiBanLe
		Values(@MaKhachHangTraLaiBanLe,
				@MaKhachHang,
				@TenKhachHang,
				@MaKho,
				@TenKho ,
				@MaNhanVien,
				@TenNhanVien,
				@NgayNhap,
				@NguoiTra,
				@MaTienTe,
				@TyGiaTienTe,
				@TienBoiThuong,
				@GhiChu,
				@Deleted)
	END
END
GO
CREATE PROC sp_Select_KhachHangTraLaiBanBuon
	@HanhDong nvarchar(50),
	@MaKhachHangTraLaiBanBuon	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From KhachHangTraLaiBanBuon
	END
	IF @HanhDong='1'
	BEGIN
		Select * From KhachHangTraLaiBanBuon Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From KhachHangTraLaiBanBuon Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From KhachHangTraLaiBanBuon Where Deleted='False' AND MaKhachHangTraLaiBanBuon=@MaKhachHangTraLaiBanBuon
	END
END
GO
CREATE PROC sp_InsertUpdate_KhachHangTraLaiBanBuon
	@HanhDong nvarchar(50),
	@MaKhachHangTraLaiBanBuon varchar(50),
	@NgayNhap	Datetime,
	@MaKhachHang	varchar(50),
	@TenKhachHang nvarchar(200),
	@DuNo float,
	@NguoiTra	nvarchar(200),
	@MaKho	varchar(50),
	@TenKho  nvarchar(200),
	@MaNhanVien	varchar(50),
	@TenNhanVien	nvarchar(200),
	@MaTienTe	varchar(50),
	@TyGiaTienTe	float,
	@Thue float,
	@TienBoiThuong	float,
	@ThanhToanNgay	float,
	@ThanhToanSauKhiLapPhieu float,
	@GhiChu	nvarchar(200),
	@Deleted	bit
AS
BEGIN
	IF @HanhDong='Insert'
	BEGIN
		Insert Into KhachHangTraLaiBanBuon
		Values(@MaKhachHangTraLaiBanBuon,
				@NgayNhap,
				@MaKhachHang,
				@TenKhachHang,
				@DuNo,
				@NguoiTra,
				@MaKho,
				@TenKho ,
				@MaNhanVien,
				@TenNhanVien,
				@MaTienTe,
				@TyGiaTienTe,
				@Thue,
				@TienBoiThuong,
				@ThanhToanNgay,
				@ThanhToanSauKhiLapPhieu,
				@GhiChu,
				@Deleted)
	END
END
GO
CREATE PROC sp_Select_ChiTietHoaDonNhap
	@HanhDong nvarchar(50),
	@MaHoaDonNhap	varchar(50),
	@MaHangHoa	varchar(50) 
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From ChiTietHoaDonNhap
	END
	IF @HanhDong='1'
	BEGIN
		Select * From ChiTietHoaDonNhap Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From ChiTietHoaDonNhap Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From ChiTietHoaDonNhap Where Deleted='False' AND MaHoaDonNhap=@MaHoaDonNhap AND MaHangHoa=@MaHangHoa
	END
	IF @HanhDong='4'
	BEGIN
		Select * From ChiTietHoaDonNhap Where Deleted='False' AND MaHoaDonNhap=@MaHoaDonNhap
	END
END
GO
CREATE PROC sp_InsertUpdate_ChiTietHoaDonNhap
	@HanhDong nvarchar(50),
	@MaHoaDonNhap	varchar(50),
	@MaHangHoa	varchar(50),
	@TenHangHoa nvarchar(200),
	@SoLuong	float,
	@PhanTramChietKhau	float,
	@DonGia	float,
	@ThanhTien	float,
	@Thue	float,
	@SoLuongConLai float,
	@HanSuDung	datetime,
	@MaDonViTinh varchar(50),
	@TenDonViTinh nvarchar(200),
	@GhiChu	nvarchar(200),
	@Deleted	bit	
AS
BEGIN
	IF @HanhDong='Insert'
	BEGIN
		Insert Into ChiTietHoaDonNhap
		Values(@MaHoaDonNhap,@MaHangHoa,@TenHangHoa,@SoLuong,@PhanTramChietKhau,@DonGia,@ThanhTien,@Thue,@SoLuongConLai,@HanSuDung,@MaDonViTinh,@TenDonViTinh,@GhiChu,@Deleted)
	END
END
GO
CREATE PROC sp_Select_ChiTietHangHoaNhaCungCap
	@HanhDong nvarchar(50),
	@MaNhaCungCap	varchar(50),
	@MaHangHoa	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From ChiTietHangHoaNhaCungCap
	END
	IF @HanhDong='1'
	BEGIN
		Select * From ChiTietHangHoaNhaCungCap Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From ChiTietHangHoaNhaCungCap Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From ChiTietHangHoaNhaCungCap Where Deleted='False' AND MaNhaCungCap=@MaNhaCungCap AND MaHangHoa=@MaHangHoa
	END
	IF @HanhDong='4'
	BEGIN
		Select * From ChiTietHangHoaNhaCungCap Where Deleted='False' AND MaNhaCungCap=@MaNhaCungCap
	END
END
GO
CREATE PROC sp_Deleted_ChiTietHangHoaNhaCungCap
	@HanhDong nvarchar(50),
	@MaNhaCungCap	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Delete From ChiTietHangHoaNhaCungCap Where MaNhaCungCap=@MaNhaCungCap
	END
	IF @HanhDong='1'
	BEGIN
		Update ChiTietHangHoaNhaCungCap 
		Set Deleted='True' 
		Where MaNhaCungCap=@MaNhaCungCap
	END
END
GO
CREATE PROC sp_InsertUpdate_ChiTietHangHoaNhaCungCap
	@HanhDong nvarchar(50),
	@MaNhaCungCap	varchar(50),
	@TenNhaCungCap nvarchar(200),
	@MaHangHoa	varchar(50),
	@TenHangHoa	nvarchar(200),
	@MaDonViTinh varchar(50),
	@TenDonViTinh nvarchar(200),
	@GhiChu	nvarchar(200),
	@Deleted	bit
AS
BEGIN
	IF @HanhDong='Insert'
	BEGIN
		Insert Into ChiTietHangHoaNhaCungCap
		Values(@MaNhaCungCap,@TenNhaCungCap,@MaHangHoa,@TenHangHoa,@MaDonViTinh,@TenDonViTinh, @GhiChu,@Deleted)
	END
	IF @HanhDong='Update'
	BEGIN
		Insert Into ChiTietHangHoaNhaCungCap
		Values(@MaNhaCungCap,@TenNhaCungCap,@MaHangHoa,@TenHangHoa,@MaDonViTinh,@TenDonViTinh, @GhiChu,@Deleted)
	END
END
GO
CREATE PROC sp_SelectHangHoa
	@HanhDong nvarchar(50),
	@MaHangHoa	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From HangHoa
	END
	IF @HanhDong='1'
	BEGIN
		Select * From HangHoa Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From HangHoa Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From HangHoa Where Deleted='False' AND MaHangHoa=@MaHangHoa
	END
END
GO
CREATE PROC sp_SelectKhoHang
	@HanhDong nvarchar(50),
	@MaKho	varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From KhoHang
	END
	IF @HanhDong='1'
	BEGIN
		Select * From KhoHang Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From KhoHang Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From KhoHang Where Deleted='False' AND MaKho=@MaKho
	END
END
GO
CREATE PROC sp_Select_DonDatHangNCC
	@HanhDong nvarchar(50),
	@MaDonDatHangNCC varchar(50),
	@MaKho varchar(50),
	@MaNhaCungCap varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From DonDatHangNCC
	END
	IF @HanhDong='1'
	BEGIN
		Select * From DonDatHangNCC Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From DonDatHangNCC Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From DonDatHangNCC Where Deleted='False' AND MaKho=@MaKho
	END
	IF @HanhDong='4'
	BEGIN
		Select * From DonDatHangNCC Where Deleted='False' AND MaKho=@MaKho AND MaDonDatHangNCC=@MaDonDatHangNCC
	END
	IF @HanhDong='5'
	BEGIN
		Select * From DonDatHangNCC Where Deleted='False' AND MaKho=@MaKho AND MaNhaCungCap=@MaNhaCungCap
	END
END
GO
CREATE PROC sp_Select_ChiTietKhoHang
	@HanhDong nvarchar(50),
	@MaKho varchar(50),
	@MaHangHoa varchar(50)
AS
BEGIN
	IF @HanhDong='0'
	BEGIN
		Select * From ChiTietKhoHang
	END
	IF @HanhDong='1'
	BEGIN
		Select * From ChiTietKhoHang Where Deleted='False'
	END
	IF @HanhDong='2'
	BEGIN
		Select * From ChiTietKhoHang Where Deleted='True'
	END
	IF @HanhDong='3'
	BEGIN
		Select * From ChiTietKhoHang Where Deleted='False' AND MaKho=@MaKho
	END
	IF @HanhDong='4'
	BEGIN
		Select * From ChiTietKhoHang Where Deleted='False' AND MaKho=@MaKho AND @MaHangHoa=@MaHangHoa
	END
END
GO
CREATE PROC sp_InsertUpdateChiTietKhoHang
	@HanhDong nvarchar(50),
	@MaKho	varchar(50),
	@TenKho  nvarchar(200),
	@MaHangHoa	varchar(50),
	@TenHangHoa nvarchar(200),
	@SoLuong	float,
	@NgayNhap	datetime,
	@HanSuDung	datetime,
	@MaDonViTinh varchar(50),
	@TenDonViTinh nvarchar(200),
	@GhiChu	nvarchar(200),
	@Deleted bit
AS
BEGIN
	Declare @count int set @count=0
	Select @count=COUNT(*) From ChiTietKhoHang Where MaKho=@MaKho AND MaHangHoa=@MaHangHoa
	IF @count>0
	BEGIN
		exec sp_CongKho @MaKho,@MaHangHoa,@SoLuong
	END
	ELSE
	BEGIN
		Insert into ChiTietKhoHang values(@MaKho,@TenKho,@MaHangHoa,@TenHangHoa,@SoLuong,@NgayNhap,@HanSuDung,@MaDonViTinh,@TenDonViTinh,@GhiChu,@Deleted)
	END
END
GO
CREATE PROC sp_Update_TrangThaiDonDatHang
	@HanhDong varchar(50),
	@MaDonDatHang varchar(50),
	@TrangThai Nvarchar(50)
AS
BEGIN
	IF @HanhDong='1'
	BEGIN
		Update DonDatHangNCC
		Set TrangThaiDonDatHang=@TrangThai
		Where MaDonDatHangNCC=@MaDonDatHang AND Deleted=N'False'
	END
	IF @HanhDong='2'
	BEGIN
		Update DonDatHangKH
		Set TrangThaiDonDatHang=@TrangThai
		Where MaDonDatHangKH=@MaDonDatHang AND Deleted=N'False'
	END
END
go
create proc sp_Update_SoLuongConLai
	@HanhDong varchar(50),
	@MaHoaDon varchar(50),
	@MaHangHoa   varchar(50),
	@SoLuongConLai float
as
begin
	IF @HanhDong='1'
	BEGIN
		 Update ChiTietHoaDonNhap
		 Set SoLuongConLai=@SoLuongConLai
		 Where MaHoaDonNhap=@MaHoaDon AND MaHangHoa=@MaHangHoa AND Deleted=N'False'
	END
	IF @HanhDong='2'
	BEGIN
		 Update ChiTietHDBanBuon
		 Set SoLuongConLai=@SoLuongConLai
		 Where MaHDBanBuon=@MaHoaDon AND MaHangHoa=@MaHangHoa AND Deleted=N'False'
	END
	IF @HanhDong='3'
	BEGIN
		 Update ChiTietHDBanle
		 Set SoLuongConLai=@SoLuongConLai
		 Where MaHDBanLe=@MaHoaDon AND MaHangHoa=@MaHangHoa AND Deleted=N'False'
	END
end
go
create proc sp_Update_DuNo
	@HanhDong nvarchar(50),
	@MaUpdate  nvarchar(50),
	@DuNo float
as
begin
	IF @HanhDong='1'
	BEGIN
		Update KhachHang
		Set DuNo=@DuNo
		Where MaKhachHang=@MaUpdate AND Deleted=N'False'
	END
	IF @HanhDong='2'
	BEGIN
		Update NhaCungCap
		Set NoHienThoi=@DuNo
		Where MaNhaCungCap=@MaUpdate AND Deleted=N'False'
	END
end
GO
CREATE PROC sp_Select_ChiTietHangHoaCacBang
	@HanhDong nvarchar(50),
	@MaKho varchar(50),
	@MaNhaCungCap varchar(50),
	@MaHoaDon varchar(50)
AS
BEGIN
	IF @HanhDong='1' -- hang hoa theo kho
	BEGIN
		Select a.MaHangHoa,a.TenHangHoa,
			   a.SoLuong,a.MaDonViTinh ,a.TenDonViTinh,
               a.HanSuDung,b.GiaNhap,b.GiaBanBuon,
               b.GiaBanLe,0 AS 'ChietKhau',0 AS 'ChietKhauThuongMai',
			   b.GiaTriThue AS 'Thue',0 AS 'SoLuongConLai',0 AS 'ThanhTien'
		From ChiTietKhoHang a  join HangHoa b
		ON a.MaHangHoa=b.MaHangHoa
		Where a.MaKho=@MaKho AND a.Deleted='False'
	END
	IF @HanhDong='2' -- hang hoa theo danh muc hang hoa
	BEGIN
		Select MaHangHoa,TenHangHoa,
			   0 as 'SoLuong',MaDonViTinh ,TenDonViTinh,
               getdate() as 'HanSuDung',GiaNhap,GiaBanBuon,
               GiaBanLe,0 AS 'ChietKhau',0 AS 'ChietKhauThuongMai',
			   GiaTriThue AS 'Thue',0 AS 'SoLuongConLai',0 AS 'ThanhTien'
		From HangHoa
		Where Deleted='False'
	END
	IF @HanhDong='3' --hang hoa theo nha cung cap
	BEGIN
		Select a.MaHangHoa,a.TenHangHoa,
			   0 as 'SoLuong',a.MaDonViTinh ,a.TenDonViTinh,
               getdate() as 'HanSuDung',b.GiaNhap,b.GiaBanBuon,
               b.GiaBanLe,0 AS 'ChietKhau',0 AS 'ChietKhauThuongMai',
			   b.GiaTriThue AS 'Thue',0 AS 'SoLuongConLai',0 AS 'ThanhTien'
		From ChiTietHangHoaNhaCungCap a join HangHoa b
		ON a.MaHangHoa=b.MaHangHoa
		Where MaNhaCungCap=@MaNhaCungCap AND a.Deleted='False'
	END
	IF @HanhDong='4' -- chi tiet hang hoa theo hoa don nhap
	BEGIN
		Select a.MaHangHoa,a.TenHangHoa,
			   a.SoLuong,a.MaDonViTinh ,a.TenDonViTinh,
               a.HanSuDung,DonGia AS 'GiaNhap',b.GiaBanBuon,
               b.GiaBanLe,a.PhanTramChietKhau AS 'ChietKhau',
			   0 AS 'ChietKhauThuongMai',
			   a.Thue AS 'Thue',SoLuongConLai,ThanhTien
		From ChiTietHoaDonNhap a join HangHoa b
		ON a.MaHangHoa=b.MaHangHoa
		Where a.MaHoaDonNhap=@MaHoaDon AND a.Deleted='False'
	END
	IF @HanhDong='5' -- chi tiet hang hoa theo don dat hang nha cung cap
	BEGIN
		Select a.MaHangHoa,a.TenHangHoa,
			   a.SoLuong,b.MaDonViTinh ,b.TenDonViTinh,
               getdate() AS 'HanSuDung',a.DonGia AS 'GiaNhap',b.GiaBanBuon,
               b.GiaBanLe,a.PhanTramChietKhau AS 'ChietKhau',
			   0 AS 'ChietKhauThuongMai',
			   a.Thue AS 'Thue',a.SoLuong AS 'SoLuongConLai',a.ThanhTien
		From ChiTietDonHangNCC a join HangHoa b
		ON a.MaHangHoa=b.MaHangHoa
		Where a.MaDonDatHangNCC=@MaHoaDon AND a.Deleted='False'
	END
	IF @HanhDong='6' -- chi tiet hang hoa theo hoa don ban buon
	BEGIN
		Select a.MaHangHoa,a.TenHangHoa,
			   a.SoLuong,b.MaDonViTinh ,b.TenDonViTinh,
               a.HanSuDung,a.DonGia AS 'GiaNhap',b.GiaBanBuon,
               b.GiaBanLe,a.PhanTramChietKhau AS 'ChietKhau',
			   0 AS 'ChietKhauThuongMai',
			   a.ThueGTGT AS 'Thue',a.SoLuongConLai,a.ThanhTien
		From ChiTietHDBanBuon a join HangHoa b
		ON a.MaHangHoa=b.MaHangHoa
		Where a.MaHDBanBuon=@MaHoaDon AND a.Deleted='False'
	END
END

